/**
 * 
 */
package fr.sinno.wms.batch.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import fr.sinno.wms.batch.model.ExportedCommand;
import fr.sinno.wms.batch.model.HeaderCommand;
import fr.sinno.wms.batch.model.LineCommand;
import fr.sinno.wms.batch.model.Stock;

/**
 * @author HP
 *
 */
@Repository
public class CommandeRepository {

	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Qualifier("jdbcTemplateStock")
	private JdbcTemplate jdbcStockTemplate;

	private static final Logger LOG = Logger.getLogger(CommandeRepository.class);

	public List<LineCommand> findCommandById(String codeDocument) {
		String sql = "SELECT * FROM LigneCdeClient WHERE CodeDocument = '" + codeDocument + "'";
		List<LineCommand> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper(LineCommand.class));

		return list;
	}

	public LineCommand findUniqueCommandById(String codeDocument, int numeroLigne) {
		String sql = "SELECT * FROM LigneCdeClient WHERE CodeDocument = '" + codeDocument + "' AND NumeroLigne = " + numeroLigne;
		List<LineCommand> resultat = jdbcTemplate.query(sql, new BeanPropertyRowMapper(LineCommand.class));
		if (null != resultat && resultat.size() > 0) {
		    return resultat.get(0);
		}
		return null;
	}

	public List<LineCommand> findCommandExported(String beginValue, String endValue) {
		String sql = "SELECT * FROM LigneCdeClient  WHERE (quantite - qteLivree) > 0 " +
				" AND TypeLigne = 'U' AND LigneSoldee = 0 "
				+ " AND [CodeDocument] > '" + beginValue + "' AND [CodeDocument] < '" + endValue + "'";
		List<LineCommand> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper(LineCommand.class));

		return list;
	}

	public int updateCommande(LineCommand commande) {
		if (null != commande.getNumeroLigne()) {
			String sql = "UPDATE LigneCdeClient SET LigneSoldee = ?, QteLivree = ? WHERE CodeDocument = ? AND NumeroLigne = ?";
			int res = jdbcTemplate.update(sql, new Object[] { commande.getLigneSoldee(),
				commande.getQteLivree(), commande.getCodeDocument(), commande.getNumeroLigne().intValue()  });
	
			return res;
		}
		return 0;
	}

	public int updateQuatiteCommande(LineCommand commande) {
		if (null != commande.getNumeroLigne()) {
			String sql = "UPDATE LigneCdeClient SET QteLivree = ? WHERE CodeDocument = ? AND NumeroLigne = ?";
			int res = jdbcTemplate.update(sql, new Object[] { commande.getQteLivree(), 
					commande.getCodeDocument(), commande.getNumeroLigne().intValue()  });
	
			return res;
		}
		return 0;
	}

	public HeaderCommand findHeaderCommandById(String codeDocument) {
		String sql = "SELECT * FROM EnteteCdeClient WHERE Code = ?";
		List<HeaderCommand> headerComm = jdbcTemplate.query(sql, new Object[] {codeDocument}, new BeanPropertyRowMapper(HeaderCommand.class));
		if (null != headerComm && headerComm.size() > 0) {
			return headerComm.get(0);
		}
		return null;
	}

	public List<HeaderCommand> findListHeaderCommandById(String codeDocument) {
		String sql = "SELECT * FROM EnteteCdeClient WHERE Code = '" + codeDocument + "'";
		List<HeaderCommand> headerComm = jdbcTemplate.query(sql, new BeanPropertyRowMapper(HeaderCommand.class));
		return headerComm;
	}

	public void commit() {
		try {
			this.jdbcTemplate.getDataSource().getConnection().commit();
			this.jdbcStockTemplate.getDataSource().getConnection().commit();
		} catch (SQLException exception) {
			LOG.error("ERREUR COMMIT  DATABASE : " + exception);
		}
	}

	public void rollBack() {
		try {
			this.jdbcTemplate.getDataSource().getConnection().rollback();
			this.jdbcStockTemplate.getDataSource().getConnection().rollback();
		} catch (SQLException exception) {
			LOG.error("ERREUR ROLLBACK : " + exception);
		}
	}

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public int updateStock(Stock stock) {
	
		int res = 0;
		try {
			jdbcStockTemplate.getDataSource().getConnection().setAutoCommit(false);
			String sql = "UPDATE Stock SET StockPhy = ?, RelCli = ? WHERE CodeArticle = ?";
	
			res = jdbcStockTemplate.update(sql, new Object[] { stock.getStockPhy(),
					stock.getRelCli(), stock.getCodeArticle()  });
		} catch (SQLException e) {
			LOG.error("ERREUR SET AUTO COMMIT A FALSE");
			return 0;
		}

		return res;
	}

	public Stock findStockById(String codeArticle) {
		String sql = "SELECT * FROM Stock WHERE CodeArticle = ?";
		List<Stock> stock = jdbcStockTemplate.query(sql, new Object[] {codeArticle}, new BeanPropertyRowMapper(Stock.class));
		if (null != stock && stock.size() > 0) {
			stock.get(0);
		}
		return null;
	}
}
